﻿namespace Hidistro.SqlDal.Commodities
{
    using Hidistro.Core;
    using Hidistro.Core.Entities;
    using Hidistro.Core.Enums;
    using Hidistro.Entities;
    using Hidistro.Entities.Commodities;
    using Hidistro.Membership.Context;
    using Hidistro.Membership.Core.Enums;
    using Hidistro.SqlDal.Members;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Runtime.InteropServices;
    using System.Text;
    using System.Text.RegularExpressions;

    public class ProductBrowseDao
    {
        private Database database = DatabaseFactory.CreateDatabase(); 

        protected string BuildAPIProductBrowseQuerySearch(ProductQuery query)
        {
            StringBuilder builder = new StringBuilder(" 1=1 ");
            if (query.SaleStatus != ProductSaleStatus.All)
            {
                builder.AppendFormat(" AND SaleStatus = {0}", (int)query.SaleStatus);
            }
            else
            {
                builder.AppendFormat(" AND SaleStatus not in ({0})", 0);
            }
            if (query.BrandId.HasValue)
            {
                builder.AppendFormat(" AND BrandId = {0}", query.BrandId.Value);
            }
            if (query.TagId.HasValue)
            {
                builder.AppendFormat("AND ProductId IN (SELECT ProductId FROM Hishop_ProductTag WHERE TagId={0})", query.TagId);
            }
            if (!string.IsNullOrEmpty(query.Keywords))
            {
                query.Keywords = DataHelper.CleanSearchString(query.Keywords);
                string[] strArray = Regex.Split(query.Keywords.Trim(), @"\s+");
                builder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[0]));
                for (int i = 1; (i < strArray.Length) && (i <= 4); i++)
                {
                    builder.AppendFormat("AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[i]));
                }
            }
            if (!string.IsNullOrEmpty(query.ProductCode))
            {
                builder.AppendFormat(" AND ProductCode LIKE '%{0}%'", DataHelper.CleanSearchString(query.ProductCode));
            }
            if (query.CategoryId.HasValue && (query.CategoryId.Value > 0))
            {
                builder.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%'  OR ExtendCategoryPath LIKE '{0}|%' )", query.MaiCategoryPath);
            }
            if (query.IsMakeTaobao.HasValue && (query.IsMakeTaobao.Value >= 0))
            {
                builder.AppendFormat(" AND IsMaketaobao={0}", query.IsMakeTaobao.Value);
            }
            if (query.PublishStatus != PublishStatus.NotSet)
            {
                if (query.PublishStatus == PublishStatus.Notyet)
                {
                    builder.Append(" AND TaobaoProductId = 0");
                }
                else
                {
                    builder.Append(" AND TaobaoProductId <> 0");
                }
            }
            if (query.StartDate.HasValue)
            {
                builder.AppendFormat(" AND AddedDate >='{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
            }
            if (query.EndDate.HasValue)
            {
                builder.AppendFormat(" AND AddedDate <='{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
            }
            return builder.ToString();
        }

        protected string BuildProductBrowseQuerySearch(ProductBrowseQuery query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("SaleStatus = {0}", (int)query.ProductSaleStatus);
            if (!query.IsPrecise)
            {
                if (!string.IsNullOrEmpty(query.ProductCode))
                {
                    builder.AppendFormat(" AND LOWER(ProductCode) Like  '%{0}%'", DataHelper.CleanSearchString(query.ProductCode).ToLower());
                }
            }
            else if (!string.IsNullOrEmpty(query.ProductCode))
            {
                builder.AppendFormat(" AND LOWER(ProductCode)='{0}'", DataHelper.CleanSearchString(query.ProductCode).ToLower());
            }
            if (query.AttributeValues.Count > 0)
            {
                foreach (AttributeValueInfo info in query.AttributeValues)
                {
                    builder.AppendFormat(" AND ProductId IN ( SELECT ProductId FROM Hishop_ProductAttributes WHERE AttributeId={0} And ValueId={1}) ", info.AttributeId, info.ValueId);
                }
            }
            if (query.BrandId.HasValue)
            {
                if (query.BrandId.Value == 0)
                {
                    builder.Append(" AND BrandId IS NOT NULL");
                }
                else
                {
                    builder.AppendFormat(" AND BrandId = {0}", query.BrandId.Value);
                }
            }
            if (query.feature.HasValue)
            {
                if (query.feature.Value == 0)
                {
                    builder.Append(" AND feature IS NOT NULL");
                }
                else
                {
                    builder.AppendFormat(" AND feature LIKE '%,{0},%'", query.feature.Value);
                }
            }
            if (query.MinSalePrice.HasValue)
            {
                builder.AppendFormat(" AND SalePrice >= {0}", query.MinSalePrice.Value);
            }
            if (query.MaxSalePrice.HasValue)
            {
                builder.AppendFormat(" AND SalePrice <= {0}", query.MaxSalePrice.Value);
            }
            if (!string.IsNullOrEmpty(query.Keywords) && (query.Keywords.Trim().Length > 0))
            {
                if (!query.IsPrecise)
                {
                    query.Keywords = DataHelper.CleanSearchString(query.Keywords);
                    string[] strArray = Regex.Split(query.Keywords.Trim(), @"\s+");
                    List<string> list = new List<string> {
                        string.Format("(replace(ProductName,' ','') LIKE '%{0}%' or LOWER(ProductCode) LIKE '%{0}%')", DataHelper.CleanSearchString(strArray[0]))
                    };
                    for (int i = 1; (i < strArray.Length) && (i <= 4); i++)
                    {
                        list.Add(string.Format("(replace(ProductName,' ','') LIKE '%{0}%' or LOWER(ProductCode) LIKE '%{0}%')", DataHelper.CleanSearchString(strArray[i])));
                    }
                    builder.Append(" and (" + string.Join(" and ", list.ToArray()) + ")");
                }
                else
                {
                    builder.AppendFormat(" AND ProductName = '{0}'", DataHelper.CleanSearchString(query.Keywords));
                }
            }
            if (query.CategoryId.HasValue)
            {
                CategoryInfo category = new CategoryDao().GetCategory(query.CategoryId.Value);
                if (category != null)
                {
                    builder.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%') ", category.Path);
                }
                else
                {
                    builder.Append(" and 1>2 ");
                }
            }
            if (!string.IsNullOrEmpty(query.TagIds))
            {
                string[] strArray2 = query.TagIds.Split(new char[] { '_' });
                foreach (string str in strArray2)
                {
                    if (!string.IsNullOrEmpty(str))
                    {
                        builder.AppendFormat(" AND ProductId IN(SELECT ProductId FROM Hishop_ProductTag WHERE TagId = {0})", str);
                    }
                }
            }
            return builder.ToString();
        }

        protected string BuildProductSubjectQuerySearch(SubjectListQuery query)
        {
            int num2;
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(" SaleStatus = {0}", 1);
            if (query.TagId != 0)
            {
                builder.AppendFormat(" AND ProductId IN(SELECT ProductId FROM Hishop_ProductTag WHERE TagId = {0})", query.TagId);
            }
            if (!string.IsNullOrEmpty(query.CategoryIds))
            {
                string[] strArray = query.CategoryIds.Split(new char[] { ',' });
                int result = 0;
                bool flag = false;
                CategoryInfo category = null;
                builder.AppendFormat(" AND (", new object[0]);
                for (num2 = 0; num2 < strArray.Length; num2++)
                {
                    result = 0;
                    int.TryParse(strArray[num2], out result);
                    category = new CategoryDao().GetCategory(result);
                    if (category != null)
                    {
                        if (flag)
                        {
                            builder.Append(" OR ");
                        }
                        builder.AppendFormat(" ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%') ", category.Path);
                        flag = true;
                    }
                }
                if (!flag)
                {
                    builder.Append("1=1");
                }
                builder.Append(")");
            }
            if (query.BrandCategoryId.HasValue)
            {
                builder.AppendFormat(" AND BrandId = {0}", query.BrandCategoryId.Value);
            }
            if (query.ProductTypeId.HasValue)
            {
                builder.AppendFormat(" AND TypeId = {0}", query.ProductTypeId.Value);
            }
            if (query.AttributeValues.Count > 0)
            {
                foreach (AttributeValueInfo info2 in query.AttributeValues)
                {
                    builder.AppendFormat(" AND (ProductId IN ( SELECT ProductId FROM Hishop_ProductAttributes WHERE AttributeId={0} And ValueId={1})) ", info2.AttributeId, info2.ValueId);
                }
            }
            if (query.MinPrice.HasValue)
            {
                builder.AppendFormat(" AND SalePrice >= {0}", query.MinPrice.Value);
            }
            if (query.MaxPrice.HasValue)
            {
                builder.AppendFormat(" AND SalePrice <= {0}", query.MaxPrice.Value);
            }
            if (!string.IsNullOrEmpty(query.Keywords) && (query.Keywords.Trim().Length > 0))
            {
                query.Keywords = DataHelper.CleanSearchString(query.Keywords);
                string[] strArray2 = Regex.Split(query.Keywords.Trim(), @"\s+");
                builder.AppendFormat(" AND (ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray2[0]));
                for (num2 = 1; (num2 < strArray2.Length) && (num2 <= 5); num2++)
                {
                    builder.AppendFormat(" OR ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray2[num2]));
                }
                builder.Append(")");
            }
            return builder.ToString();
        }

        protected string BuildProductSubjectQuerySearchTop(SubjectListQuery query)
        {
            int num2;
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(" SaleStatus = {0}", 1);
            if (query.TagId != 0)
            {
                builder.AppendFormat(" AND ProductId Not IN(SELECT ProductId FROM Hishop_ProductTag WHERE TagId = {0})", query.TagId);
            }
            if (!string.IsNullOrEmpty(query.CategoryIds))
            {
                string[] strArray = query.CategoryIds.Split(new char[] { ',' });
                int result = 0;
                bool flag = false;
                CategoryInfo category = null;
                builder.AppendFormat(" AND (", new object[0]);
                for (num2 = 0; num2 < strArray.Length; num2++)
                {
                    result = 0;
                    int.TryParse(strArray[num2], out result);
                    category = new CategoryDao().GetCategory(result);
                    if (category != null)
                    {
                        if (flag)
                        {
                            builder.Append(" OR ");
                        }
                        builder.AppendFormat(" ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%') ", category.Path);
                        flag = true;
                    }
                }
                if (!flag)
                {
                    builder.Append("1=1");
                }
                builder.Append(")");
            }
            if (query.BrandCategoryId.HasValue)
            {
                builder.AppendFormat(" AND BrandId = {0}", query.BrandCategoryId.Value);
            }
            if (query.ProductTypeId.HasValue)
            {
                builder.AppendFormat(" AND TypeId = {0}", query.ProductTypeId.Value);
            }
            if (query.AttributeValues.Count > 0)
            {
                foreach (AttributeValueInfo info2 in query.AttributeValues)
                {
                    builder.AppendFormat(" AND (ProductId IN ( SELECT ProductId FROM Hishop_ProductAttributes WHERE AttributeId={0} And ValueId={1})) ", info2.AttributeId, info2.ValueId);
                }
            }
            if (query.MinPrice.HasValue)
            {
                builder.AppendFormat(" AND SalePrice >= {0}", query.MinPrice.Value);
            }
            if (query.MaxPrice.HasValue)
            {
                builder.AppendFormat(" AND SalePrice <= {0}", query.MaxPrice.Value);
            }
            if (!string.IsNullOrEmpty(query.Keywords) && (query.Keywords.Trim().Length > 0))
            {
                query.Keywords = DataHelper.CleanSearchString(query.Keywords);
                string[] strArray2 = Regex.Split(query.Keywords.Trim(), @"\s+");
                builder.AppendFormat(" AND (ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray2[0]));
                for (num2 = 1; (num2 < strArray2.Length) && (num2 <= 5); num2++)
                {
                    builder.AppendFormat(" OR ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray2[num2]));
                }
                builder.Append(")");
            }
            return builder.ToString();
        }

        public DbQueryResult GetBatchBuyProducts(ProductQuery query)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SaleStatus=1");
            if (!string.IsNullOrEmpty(query.Keywords))
            {
                query.Keywords = DataHelper.CleanSearchString(query.Keywords);
                string[] strArray = Regex.Split(query.Keywords.Trim(), @"\s+");
                builder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[0]));
                for (int i = 1; (i < strArray.Length) && (i <= 4); i++)
                {
                    builder.AppendFormat("AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[i]));
                }
            }
            if (query.CategoryId.HasValue && (query.CategoryId.Value > 0))
            {
                builder.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%')", query.MaiCategoryPath);
            }
            if (query.BrandId.HasValue)
            {
                builder.AppendFormat(" AND BrandId = {0}", query.BrandId.Value);
            }
            if (!string.IsNullOrEmpty(query.ProductCode))
            {
                builder.AppendFormat(" AND SKU LIKE '%{0}%'", DataHelper.CleanSearchString(query.ProductCode));
            }
            Member user = HiContext.Current.User as Member;
            int discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
            StringBuilder builder2 = new StringBuilder();
            builder2.Append("SkuId, ProductId, SKU,ProductName,ProductText, ThumbnailUrl40, DisplaySequence, Stock,");
            builder2.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = {0}) = 1", user.GradeId);
            builder2.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = {0})", user.GradeId);
            builder2.AppendFormat(" ELSE SalePrice * {0} /100 END) AS SalePrice", discount);
            return DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_Hishop_ProductSkuList s", "SkuId", builder.ToString(), builder2.ToString());
        }
        public DataTable GetJsProducts(int top, int categoryid,int level)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select top {0} a.*,b.SalePrice,ROW_NUMBER() over(order by getdate()) as row_numbers", top);
            sb.AppendFormat(",ReviewText=(select top 1 ReviewText from Hishop_ProductReviews where Hishop_ProductReviews.ProductId=a.ProductId)");
            sb.AppendFormat("from Hishop_Products a  left join dbo.Hishop_SKUs as b on a.ProductId=b.ProductId");
            if (level == 1)
            {
                if (categoryid != 0)
                {
                    sb.AppendFormat(" where MainCategoryPath like '%{0}|%'", categoryid);
                }
            }
            else
            {
                if (categoryid != 0)
                {
                    sb.AppendFormat(" where MainCategoryPath like '%|{0}|%'", categoryid);
                }
 
            }

            sb.AppendFormat(" order by ShowSaleCounts desc");
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(sb.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }
        public DataTable GetJsProductList(int page, int categoryid, out int count)
        {
            DbCommand sqlStringCommand1 = this.database.GetSqlStringCommand("select ProductId  from  Hishop_Products  where MainCategoryPath like '%|" + categoryid + "|%'");
            DataTable dt=new DataTable();
            using (IDataReader reader1 = this.database.ExecuteReader(sqlStringCommand1))
            {
                dt = DataHelper.ConverDataReaderToDataTable(reader1);
            } 
            count = dt.Rows.Count;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select * from (");
            sb.AppendFormat("select a.*,b.SalePrice,ROW_NUMBER() over(order by getdate()) as row_numbers");
            sb.AppendFormat(",ReviewText=(select top 1 ReviewText from Hishop_ProductReviews where Hishop_ProductReviews.ProductId=a.ProductId)");
            sb.AppendFormat("from Hishop_Products a  left join dbo.Hishop_SKUs as b on a.ProductId=b.ProductId");
            if (categoryid != 0)
            {
                sb.AppendFormat(" where MainCategoryPath like '%|{0}|%'", categoryid);
            }
            sb.AppendFormat(") as tables");
            int n = page * 15;
            int m = n - 15;
            sb.AppendFormat(" where row_numbers between {0} and {1}", m, n);
            sb.AppendFormat(" order by ShowSaleCounts desc");
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(sb.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }
        public DataTable GetBrandProducts(int? brandId, int pageNumber, int maxNum, out int total)
        {
            Member user = HiContext.Current.User as Member;
            int discount = 100;
            StringBuilder builder = new StringBuilder();
            builder.Append("ProductId,ProductName,ProductText,ProductCode,ShowSaleCounts AS SaleCounts,ShortDescription,ShowSaleCounts,");
            builder.Append(" ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,MarketPrice,");
            if (user != null)
            {
                discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
                builder.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = {0}) = 1", user.GradeId);
                builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice", user.GradeId, discount);
            }
            else
            {
                builder.Append("SalePrice");
            }
            StringBuilder builder2 = new StringBuilder();
            builder2.Append(" SaleStatus=1");
            if (brandId.HasValue)
            {
                builder2.AppendFormat(" AND BrandId = {0}", brandId);
            }
            DbQueryResult result = DataHelper.PagingByRownumber(pageNumber, maxNum, "DisplaySequence", SortAction.Desc, true, "vw_Hishop_BrowseProductList s", "ProductId", builder2.ToString(), builder.ToString());
            DataTable data = (DataTable)result.Data;
            total = result.TotalRecords;
            return data;
        }

        public DbQueryResult GetBrowseProductList(ProductBrowseQuery query)
        {
            string filter = this.BuildProductBrowseQuerySearch(query);
            string selectFields = "ProductId,ProductName,ProductText,ProductCode, ShowSaleCounts AS SaleCounts, ShowSaleCounts, ShortDescription, ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160, ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310, ImageUrl1, ImageUrl2, ImageUrl3, ImageUrl4, ImageUrl5,ImageUrl6,ImageUrl7,ImageUrl8, MarketPrice, SalePrice,Stock, SkuId,MonthlySales,Review,BrandName,CategoryUrl";
            if (HiContext.Current.User.UserRole == UserRole.Member)
            {
                Member user = HiContext.Current.User as Member;
                int discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
                selectFields = selectFields + string.Format(",CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) = 1 ", user.GradeId) + string.Format("THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END AS RankPrice", user.GradeId, discount);
            }
            else
            {
                selectFields = selectFields + ",SalePrice as RankPrice";
            }
            return DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_Hishop_BrowseProductList p", "ProductId", filter, selectFields);
        }
        public DataTable GetProductReview(int pid)
        {
            DbCommand sqlStringCommand1 = this.database.GetSqlStringCommand("select p.ProductName,[ReviewMeta_Title],[ReviewMeta_Description],[ReviewMeta_Keywords] from Hishop_Products p left join [JS_ProductsReview] r on r.ProductId=p.ProductId  where p. ProductId=" + pid);
            DataTable dt = new DataTable();
            using (IDataReader reader1 = this.database.ExecuteReader(sqlStringCommand1))
            {
                dt = DataHelper.ConverDataReaderToDataTable(reader1);
            }

            return dt;
        }
        public ProductBrowseInfo GetProductBrowseInfo(int productId, int? maxReviewNum, int? maxConsultationNum)
        {
            int discount = 100;
            ProductBrowseInfo info = new ProductBrowseInfo();
            StringBuilder builder = new StringBuilder();
            builder.Append("UPDATE Hishop_Products SET VistiCounts = VistiCounts + 1 WHERE ProductId = @ProductId;");
            builder.Append(" SELECT p.* ,CASE WHEN BrandId IS NULL THEN NULL ELSE (SELECT BrandName FROM Hishop_BrandCategories WHERE BrandId= p.BrandId) END AS BrandName,t3.RewriteName as CategoryUrl");
            builder.Append(" FROM Hishop_Products p left join Hishop_Categories t2 on t2.CategoryId = p.CategoryId left join Hishop_Categories t3 on t3.CategoryId = t2.ParentCategoryId where ProductId=@ProductId;");
            if (HiContext.Current.User.UserRole == UserRole.Member)
            {
                Member user = HiContext.Current.User as Member;
                discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
                builder.Append("SELECT SkuId, ProductId, SKU,Weight, Stock, CostPrice,");
                builder.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = {0}) = 1", user.GradeId);
                builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice", user.GradeId, discount);
                builder.Append(" FROM Hishop_SKUs s WHERE ProductId = @ProductId");
            }
            else
            {
                builder.Append("SELECT SkuId, ProductId, SKU,Weight, Stock, CostPrice, SalePrice FROM Hishop_SKUs WHERE ProductId = @ProductId");
            }
            if (maxReviewNum.HasValue)
            {
                builder.AppendFormat(" SELECT TOP {0} * FROM Hishop_ProductReviews where ProductId=@ProductId ORDER BY ReviewId DESC; ", maxReviewNum);
                builder.Append(" SELECT Count(*) FROM Hishop_ProductReviews where ProductId=@ProductId; ");
            }
            else
            {
                builder.Append(" SELECT * FROM Hishop_ProductReviews where ProductId=@ProductId ORDER BY ReviewId DESC; ");
                builder.Append(" SELECT Count(*) FROM Hishop_ProductReviews where ProductId=@ProductId; ");
            }
            if (maxConsultationNum.HasValue)
            {
                builder.AppendFormat(" SELECT TOP {0} * FROM Hishop_ProductConsultations where ProductId=@ProductId AND ReplyUserId IS NOT NULL ORDER BY ConsultationId DESC ;", maxConsultationNum);
                builder.Append(" SELECT Count(*) FROM Hishop_ProductConsultations where ProductId=@ProductId AND ReplyUserId IS NOT NULL ; ");
            }
            else
            {
                builder.Append(" SELECT * FROM Hishop_ProductConsultations where ProductId=@ProductId AND ReplyUserId IS NOT NULL ORDER BY ConsultationId DESC ;");
                builder.Append(" SELECT Count(*) FROM Hishop_ProductConsultations where ProductId=@ProductId AND ReplyUserId IS NOT NULL ; ");
            }
            builder.Append(" SELECT a.AttributeId, AttributeName, ValueStr FROM Hishop_ProductAttributes pa JOIN Hishop_Attributes a ON pa.AttributeId = a.AttributeId");
            builder.Append(" JOIN Hishop_AttributeValues v ON a.AttributeId = v.AttributeId AND pa.ValueId = v.ValueId  WHERE ProductId = @ProductId ORDER BY a.DisplaySequence DESC, v.DisplaySequence DESC");

            builder.Append(" (SELECT w.Weight, s.SkuId, a.AttributeId, AttributeName, UseAttributeImage, av.ValueId, ValueStr, ImageUrl, w.ProductId FROM Hishop_SKUItems s join Hishop_Attributes a on s.AttributeId = a.AttributeId join Hishop_AttributeValues av on s.ValueId = av.ValueId join Hishop_SKUs w on w.SkuId = s.SkuId and w.ProductId = @ProductId) UNION ALL (SELECT w.Weight, s.SkuId, a.AttributeId, AttributeName, UseAttributeImage, av.ValueId, ValueStr, ImageUrl, w.ProductId FROM Hishop_SKUItems s join Hishop_Attributes a on s.AttributeId = a.AttributeId join Hishop_AttributeValues av on s.ValueId = av.ValueId join (select * from (select t2.* from (select ProductId from wnxd_ProductSimilar where SimilarId = (select SimilarId from wnxd_ProductSimilar where ProductId = @ProductId) and ProductId != @ProductId) t1 left join Hishop_SKUs t2 on t2.ProductId = t1.ProductId) t1 where SKU in (select min(t1.SKU) from (select t2.* from (select ProductId from wnxd_ProductSimilar where SimilarId = (select SimilarId from wnxd_ProductSimilar where ProductId = @ProductId) and ProductId != @ProductId) t1 left join Hishop_SKUs t2 on t2.ProductId = t1.ProductId) t1 group by t1.ProductId)) w on w.SkuId = s.SkuId) ORDER BY AttributeName asc, Weight desc;");
            //builder.Append(" SELECT s.SkuId, a.AttributeId, AttributeName, UseAttributeImage, av.ValueId, ValueStr, ImageUrl, w.Pcode, pd.ProductId as Pid FROM Hishop_SKUItems s join Hishop_Attributes a on s.AttributeId = a.AttributeId join Hishop_AttributeValues av on s.ValueId = av.ValueId join Hishop_SKUs w on w.SkuId = s.SkuId and w.ProductId = @ProductId left join Hishop_Products pd on pd.ProductCode = w.Pcode and pd.SaleStatus = 1 ORDER BY a.DisplaySequence DESC,av.DisplaySequence DESC;");
            builder.Append(" SELECT TOP 20 ProductId,ProductName,ProductText,ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160, ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,MarketPrice,SalePrice FROM vw_Hishop_BrowseProductList");
            builder.AppendFormat(" WHERE SaleStatus = {0} AND ProductId IN (SELECT RelatedProductId FROM Hishop_RelatedProducts WHERE ProductId = {1}) ORDER BY DisplaySequence DESC;", 1, productId);
            builder.Append("SELECT TOP 20 ProductId,ProductName,ProductText,ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160, ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,MarketPrice,SalePrice FROM vw_Hishop_BrowseProductList");
            builder.AppendFormat(" WHERE SaleStatus = {0} AND ProductId<>{1}  AND CategoryId = (SELECT CategoryId FROM Hishop_Products WHERE ProductId={1} AND SaleStatus = {0})", 1, productId);
            builder.AppendFormat(" AND ProductId NOT IN (SELECT RelatedProductId FROM Hishop_RelatedProducts WHERE ProductId = {0})", productId);
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, productId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info.Product = DataMapper.PopulateProduct(reader);
                    if (reader["BrandName"] != DBNull.Value)
                    {
                        info.BrandName = (string)reader["BrandName"];
                    }
                }
                if (reader.NextResult())
                {
                    while (reader.Read())
                    {
                        info.Product.Skus.Add((string)reader["SkuId"], DataMapper.PopulateSKU(reader));
                    }
                }
                if (reader.NextResult())
                {
                    info.DBReviews = DataHelper.ConverDataReaderToDataTable(reader);
                }
                if (reader.NextResult() && reader.Read())
                {
                    info.ReviewCount = (int)reader[0];
                }
                if (reader.NextResult())
                {
                    info.DBConsultations = DataHelper.ConverDataReaderToDataTable(reader);
                }
                if (reader.NextResult() && reader.Read())
                {
                    info.ConsultationCount = (int)reader[0];
                }
                if (reader.NextResult())
                {
                    DataTable table = DataHelper.ConverDataReaderToDataTable(reader);
                    if ((table != null) && (table.Rows.Count > 0))
                    {
                        DataTable table2 = table.Clone();
                        foreach (DataRow row in table.Rows)
                        {
                            bool flag = false;
                            if (table2.Rows.Count > 0)
                            {
                                foreach (DataRow row2 in table2.Rows)
                                {
                                    if (((int)row2["AttributeId"]) == ((int)row["AttributeId"]))
                                    {
                                        DataRow row4;
                                        flag = true;
                                        (row4 = row2)["ValueStr"] = row4["ValueStr"] + ", " + row["ValueStr"];
                                    }
                                }
                            }
                            if (!flag)
                            {
                                DataRow row3 = table2.NewRow();
                                row3["AttributeId"] = row["AttributeId"];
                                row3["AttributeName"] = row["AttributeName"];
                                row3["ValueStr"] = row["ValueStr"];
                                table2.Rows.Add(row3);
                            }
                        }
                        info.DbAttribute = table2;
                    }
                }
                if (reader.NextResult())
                {
                    info.DbSKUs = DataHelper.ConverDataReaderToDataTable(reader);
                }
                if (reader.NextResult())
                {
                    info.DbCorrelatives = DataHelper.ConverDataReaderToDataTable(reader);
                }
                if (reader.NextResult())
                {
                    info.DbCorrelatives.Merge(DataHelper.ConverDataReaderToDataTable(reader), true);
                }
            }
            return info;
        }

        public DataTable GetProducts(int? topicId, int? categoryId, string keyWord, int feature, int pageNumber, int maxNum, out int toal, string sort, bool isAsc = false)
        {
            Member user = HiContext.Current.User as Member;
            int discount = 100;
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("ProductId,ProductName,ProductText,ProductCode,ShowSaleCounts AS SaleCounts,ShortDescription,", maxNum);
            builder.Append(" ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,MarketPrice,VistiCounts,BrandName,");
            if (user != null)
            {
                discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
                builder.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = vw_Hishop_BrowseProductList.SkuId AND GradeId = {0}) = 1", user.GradeId);
                builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = vw_Hishop_BrowseProductList.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice", user.GradeId, discount);
            }
            else
            {
                builder.Append("SalePrice");
            }
            StringBuilder builder2 = new StringBuilder();
            builder2.Append(" SaleStatus=1");
            if (topicId.HasValue)
            {
                builder2.AppendFormat(" AND ProductId IN (SELECT RelatedProductId FROM Vshop_RelatedTopicProducts WHERE TopicId = {0})", topicId.Value);
            }
            if (categoryId.HasValue)
            {
                CategoryInfo category = new CategoryDao().GetCategory(categoryId.Value);
                if (category != null)
                {
                    builder2.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%') ", category.Path);
                }
            }
            if (!string.IsNullOrEmpty(keyWord))
            {
                builder2.AppendFormat(" AND (ProductName LIKE '%{0}%' OR ProductCode LIKE '%{0}%')", keyWord);
            }
            if (feature != 0) builder2.AppendFormat(" AND feature LIKE '%,{0},%'", feature);
            if (string.IsNullOrWhiteSpace(sort))
            {
                sort = "ProductId";
            }
            DbQueryResult result = DataHelper.PagingByRownumber(pageNumber, maxNum, sort, isAsc ? SortAction.Asc : SortAction.Desc, true, "vw_Hishop_BrowseProductList", "ProductId", builder2.ToString(), builder.ToString());
            DataTable data = (DataTable)result.Data;
            toal = result.TotalRecords;
            return data;
        }

        public DataTable GetSaleProductRanking(int? categoryId, int maxNum)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("SELECT TOP {0} ProductId, ProductName,ProductText, ProductCode, ShowSaleCounts AS SaleCounts, ThumbnailUrl40, ThumbnailUrl60, ThumbnailUrl100,", maxNum);
            builder.AppendFormat("  ThumbnailUrl160, ThumbnailUrl180, ThumbnailUrl220, SalePrice, MarketPrice FROM vw_Hishop_BrowseProductList WHERE SaleStatus = {0}", 1);
            if (categoryId.HasValue)
            {
                CategoryInfo category = new CategoryDao().GetCategory(categoryId.Value);
                if (category != null)
                {
                    builder.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%') ", category.Path);
                }
            }
            builder.Append("ORDER BY ShowSaleCounts DESC");
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public DataTable GetSubjectList(SubjectListQuery query)
        {
            StringBuilder builder = new StringBuilder();
            if (HiContext.Current.User.UserRole == UserRole.Member)
            {
                Member user = HiContext.Current.User as Member;
                int discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
                builder.AppendFormat("SELECT TOP {0} ProductId,ProductName,ProductText,ProductCode,ShowSaleCounts AS SaleCounts,ShortDescription,Meta_Keywords,SkuId,Stock,", query.MaxNum);
                builder.Append(" ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,ImageUrl1,ImageUrl6,ImageUrl7,ImageUrl8,MarketPrice,SalePrice,BrandId,BrandName,");
                builder.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) = 1 ", user.GradeId);
                builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END AS RankPrice,MonthlySales,Review,CategoryUrl", user.GradeId, discount);
            }
            else
            {
                builder.AppendFormat("SELECT TOP {0} ProductId,ProductName,ProductText,ProductCode,ShowSaleCounts AS SaleCounts,ShortDescription,Meta_Keywords,SkuId,Stock,", query.MaxNum);
                builder.Append(" ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,ImageUrl1,ImageUrl6,ImageUrl7,ImageUrl8,MarketPrice,SalePrice,SalePrice AS RankPrice,BrandId,BrandName,MonthlySales,Review,CategoryUrl");
            }
            builder.Append(" FROM vw_Hishop_BrowseProductList p WHERE ");
            builder.Append(this.BuildProductSubjectQuerySearch(query));
            builder.Append(" ORDER BY ");
            if (!string.IsNullOrEmpty(query.SortBy))
            {
                builder.AppendFormat("{0} {1}, ", DataHelper.CleanSearchString(query.SortBy), DataHelper.CleanSearchString(query.SortOrder.ToString()));
            }
            builder.Append("Stock DESC");
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public DataTable GetSubjectListTop(SubjectListQuery query)
        {
            StringBuilder builder = new StringBuilder();
            if (HiContext.Current.User.UserRole == UserRole.Member)
            {
                Member user = HiContext.Current.User as Member;
                int discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
                builder.AppendFormat("SELECT TOP {0} ProductId,ProductName,ProductText,ProductCode,ShowSaleCounts AS SaleCounts,ShortDescription,Meta_Keywords,", query.MaxNum);
                builder.Append(" ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,ImageUrl1,ImageUrl6,ImageUrl7,ImageUrl8,MarketPrice,SalePrice,");
                builder.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) = 1 ", user.GradeId);
                builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END AS RankPrice,CategoryUrl", user.GradeId, discount);
            }
            else
            {
                builder.AppendFormat("SELECT TOP {0} ProductId,ProductName,ProductText,ProductCode,ShowSaleCounts AS SaleCounts,ShortDescription,Meta_Keywords,", query.MaxNum);
                builder.Append(" ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,ImageUrl1,ImageUrl6,ImageUrl7,ImageUrl8,MarketPrice,SalePrice,SalePrice AS RankPrice,CategoryUrl");
            }
            builder.Append(" FROM vw_Hishop_BrowseProductList p WHERE ");
            builder.Append(this.BuildProductSubjectQuerySearchTop(query));
            if (!string.IsNullOrEmpty(query.SortBy))
            {
                builder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), DataHelper.CleanSearchString(query.SortOrder.ToString()));
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public DataTable GetTopicProducts(int topicid, int maxNum)
        {
            Member user = HiContext.Current.User as Member;
            int discount = 100;
            StringBuilder builder = new StringBuilder();
            builder.Append("select top " + maxNum);
            builder.Append(" p.ProductId, ProductCode, ProductName,ProductText,ShortDescription,ShowSaleCounts,ThumbnailUrl40,ThumbnailUrl100,ThumbnailUrl160,MarketPrice,");
            if (user != null)
            {
                discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
                builder.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) = 1", user.GradeId);
                builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice, ", user.GradeId, discount);
            }
            else
            {
                builder.Append("SalePrice,");
            }
            builder.Append("SaleCounts, Stock,t.DisplaySequence from vw_Hishop_BrowseProductList p inner join  Vshop_RelatedTopicProducts t on p.productid=t.RelatedProductId where t.topicid=" + topicid);
            builder.AppendFormat(" and SaleStatus = {0}", 1);
            builder.Append(" order by t.DisplaySequence asc");
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            return this.database.ExecuteDataSet(sqlStringCommand).Tables[0];
        }

        public DataTable GetVistiedProducts(IList<int> productIds)
        {
            if (productIds.Count == 0)
            {
                return null;
            }
            string str = string.Empty;
            for (int i = 0; i < productIds.Count; i++)
            {
                str = str + productIds[i] + ",";
            }
            str = str.Remove(str.Length - 1);
            Member user = HiContext.Current.User as Member;
            StringBuilder sb = new StringBuilder();
            if (user != null)
            {
                int discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
                sb.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = b.SkuId AND GradeId = {0}) = 1", user.GradeId);
                sb.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = b.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice", user.GradeId, discount);
            }
            else
            {
                sb.Append("SalePrice");
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("SELECT ProductId,ProductName,ProductText,ProductCode,BrandName,ShowSaleCounts AS SaleCounts, ShortDescription,ThumbnailUrl40, ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl310,MarketPrice,{1},Meta_Keywords,SkuId,Stock,MonthlySales,Review,CategoryUrl  FROM  vw_Hishop_BrowseProductList as b WHERE ProductId IN({0})", str, sb.ToString()));
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public DataTable GetJsProductsReviews(int ProductId)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select count(*) from dbo.Hishop_ProductReviews where ProductId={0}", ProductId);
            sb.AppendFormat(" order by ReviewDate desc");
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(sb.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public DataTable GetJsProductsForCategory(int top, int ProductId)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select top {0} * from vw_Hishop_BrowseProductList where CategoryId=(select CategoryId from dbo.vw_Hishop_BrowseProductList where ProductId={1})", top, ProductId);
            sb.AppendFormat(" order by ShowSaleCounts desc");
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(sb.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public CategoryInfo GetProductCategory(int categoryId)
        {
            string sql = "select CategoryId,Name,DisplaySequence,Meta_Title,Meta_Description,Meta_Keywords,ParentCategoryId,Depth,Path,RewriteName,(select RewriteName from Hishop_Categories WHERE CategoryId = t1.ParentCategoryId) as ParentRewriteName,SKUPrefix,AssociatedProductType,Notes1,Notes2,Notes3,Notes4,Notes5,Theme,HasChildren,Icon from Hishop_Categories t1 WHERE CategoryId =@CategoryId";
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(sql);

            this.database.AddInParameter(sqlStringCommand, "CategoryId", DbType.Int32, categoryId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return ReaderConvert.ReaderToModel<CategoryInfo>(reader);
            }
        }
    }
}

